#!/usr/bin/env python

import os, sys
from wsgiref.simple_server import make_server
import simplejson
from sqlite3 import dbapi2 as sqlite
import urllib

from wsgiapp import BaseWsgiApp

class Crud(BaseWsgiApp):
    def __init__(self):
        BaseWsgiApp.__init__(self)
        dbfile = os.path.join(os.getcwd(), 'crud1.sqlite')
#        self.db = MySQLdb.connect(host='localhost', user='root', db='medcron', use_unicode = False)
        self.db = sqlite.connect(dbfile)
        self.cursor = self.db.cursor()
        
    def index(self, *args, **kwargs):
        self.start_response('200 OK', [('Content-Type', 'text/html')])
        f = open(os.path.join(os.getcwd(), 'index.html'), 'rb')
        return [str(f.read())]

    def list(self, desde, cuantos, where = "1", orderby = ""):
        if orderby:
            orderby = "order by %s " % orderby
            orderby = orderby.decode("utf-8")
        else:
            orderby = "order by id ".decode("utf-8")
        
        if where:
            where = where.decode("utf-8")
            if "=" in where:
                listwhere = where.split("=")
                where = "where " + listwhere[0] + " like '" + listwhere[1] + "%' "
            else:
                where = "where " + where + " "
        
        table = self.params.get("tablename","afiliados")
        
        try:
            if getattr(table, '__getitem__'):
                table = table[0]
        except:
            pass
        
        table = table.decode("utf-8")
        
        try:
            tmpfields = self.params["fields"][0].decode("utf-8")
            os.sys.stderr.write("\nUtilizando decode(utf-8) sobre self.params[fields][0]\n")
        except Exception, msg:
            os.sys.stderr.write("\nError al utilizar decode(utf-8) sobre self.params[fields][0]: %s\n" % str(msg))
            try:
                tmpfields = self.params["fields"][0].decode("latin-1")
                os.sys.stderr.write("\nUtilizando decode(latin-1) sobre self.params[fields][0]\n")
            except Exception, msg:
                os.sys.stderr.write("\nError al utilizar decode(latin-1) sobre self.params[fields][0]: %s\n" % str(msg))
                return self.server_error(msg=str(msg))

        try:
            tmpfields = urllib.unquote(tmpfields)
        except Exception, msg:
            os.sys.stderr.write("Error al utilizar urllib.unquote: %s" % str(msg))
            return self.server_error(msg=str(msg))
        
        try:
            fields = simplejson.loads(tmpfields, "utf-8")
        except Exception, msg:
            os.sys.stderr.write("Error al utilizar simplejson: %s" % str(msg))
            return self.server_error(msg=str(msg))
        
        try:
            self.cursor.execute("select count(*) from %s %s" % (table, where))
        except Exception, msg:
            os.sys.stderr.write("Error al obtener el numero de registros:\n%s" % str(msg))
            return self.server_error(msg=str(msg))
        
        total = int(self.cursor.fetchone()[0])
        numpages = (total + 0.0) / int(cuantos)
        if numpages > int(numpages):
            numpages = int(numpages) + 1
        else:
            numpages = int(numpages)
            
        if int(desde) < 1:
            desde = "1"
        if int(desde) > numpages:
            desde = str(numpages)
            
        params = (where, orderby, (int(desde) - 1) * int(cuantos), cuantos)
        sql = "select "
        for f in fields:
            sql += "%s, " % f['colName'].encode("utf-8")
        sql = sql[:-2]
        sql += " from " + table + " %s %s limit %s, %s" % params
        os.sys.stderr.write("\n\n\n" + sql + "\n\n\n")

        try:
            self.cursor.execute(sql)
        except Exception, msg:
            os.sys.stderr.write("Error:\n%s" % str(msg))
            return self.server_error(msg=str(msg))
        
        campos = []
        for i in self.cursor.description:
            campos.append(i[0])
        rs = self.cursor.fetchall()
        lrows = []
        for row in rs:
            lcols = []
            for col in row:
                if str(type(col)) == "<type 'unicode'>":
                    lcols.append(col.encode('utf-8'))
                else:
                    lcols.append(str(col))
            lrows.append(lcols)
        
        dic = {'fields': campos, 'rows': lrows, 'total': total, 'page': desde, 'numpages': numpages}

        self.start_response('200 OK', [('Content-Type', 'text/plain')])

        return [simplejson.dumps(dic)]
    
    def insert(self):
        tablename = self.params["tablename"][0]
        tmpfields = urllib.unquote(self.params["fields"][0])
        fields = simplejson.loads(tmpfields)

        sql = "insert into " + tablename + " ("
        for f in fields:
            if not f['isId']:
                sql += f['colName'].encode("utf-8") + ", "
        sql = sql[:-2]
        sql += ") values ("
        for f in fields:
            if not f['isId']:
                sql += "'" + f['colValue'].encode("utf-8") + "', "
        sql = sql[:-2]
        sql += ")"
        os.sys.stderr.write("\n\n" + sql + "\n\n")

        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.start_response('200 OK', [('Content-Type', 'text/plain')])
            return ["Record successfully inserted"]
        except:
            self.start_response('500 SERVER ERROR', [('Content-Type', 'text/plain')])
            return ["Insertion error"]
            
    def edit(self):
        tablename = self.params["tablename"][0]
        tmpfields = urllib.unquote(self.params["fields"][0])
        fields = simplejson.loads(tmpfields)
        for f in fields:
            if f['isId']:
                id = f['colValue']
                break
            
        sql = "update " + tablename + " set "
        for f in fields:
            if not f['isId']:
                sql += "%s = '%s', " % (f['colName'].encode('utf-8'), f['colValue'].encode('utf-8'))
        sql = sql[:-2]
        sql +=  " where id = %s" % id
        os.sys.stderr.write("\n\n" + sql + "\n\n")
        
        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.start_response('200 OK', [('Content-Type', 'text/plain')])
            return ["Record successfully updated"]
        except:
            self.start_response('500 SERVER ERROR', [('Content-Type', 'text/plain')])
            return ["Update error"]
            
    def delete(self, id):
        tablename = self.params["tablename"][0]
        sql = "delete from " + tablename + " where id = %s" % id
        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.start_response('200 OK', [('Content-Type', 'text/plain')])
            return ["Record successfully deleted"]
        except:
            self.start_response('500 SERVER ERROR', [('Content-Type', 'text/plain')])
            return ["Deletion error"]
            

def test():
    port = 8500
    if len(sys.argv) > 1:
        try:
            port = int(sys.argv[1])
        except:
            pass
    host = '0.0.0.0'    
    srv = make_server(host, port, Crud())
    print "%s serving at port %d ..." % (host, port)
    srv.serve_forever()


if __name__ == '__main__':
    test()
